package com.kandinfo.base.common.util;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.kandinfo.base.common.dto.EntityColumnBean;


/**
 * 
 * @author 琪伟
 *
 */
public class BaseJdbcRepository {
	private static Logger log = LoggerFactory.getLogger(BaseJdbcRepository.class);

	
	/**
	 * 执行批量sql语句
	 * @param sql
	 * @return
	 */
	public static int[] executeBatch(List<?> eneityList ,Connection connection) {
		PreparedStatement statement = null;
		int[] executeCount = new int[0];
		try {
			connection.setAutoCommit(false);
			Map<Integer, Map<Integer, Object>> map = JdbcBeanUtil.getInsertSqlByList(eneityList);
			String sql = (String) map.get(0).get(0);// sql
			Map<Integer, Object> paraMap = map.get(1);// 参数

			statement = connection.prepareStatement(sql);
			Object[] indexArray = paraMap.keySet().toArray();
			Arrays.sort(indexArray);
			for (Object objIndex : indexArray) {
				Integer index = (Integer) objIndex;
				Object object = paraMap.get(index);
				JdbcBeanUtil.setPreparedStatementVal(statement,index,object);
			}
			statement.execute();
			connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				log.error("", e1);
			}
			log.error("执行批量sql语句", e);
		} finally {
			try {
				connection.setAutoCommit(true);
			} catch (SQLException e) {
				log.error("", e);
			}
			JdbcConnection.close(null, statement, connection);
		}
		return executeCount;
	}
	
	
	
	public static int[] executeBatch(List<?> eneityList,String tableName,Connection connection) {
		PreparedStatement statement = null;
		int[] executeCount = new int[0];
		try {
			connection.setAutoCommit(false);
			Map<Integer, Map<Integer, Object>> map = JdbcBeanUtil.getInsertSqlByList(eneityList,tableName);
			String sql = (String) map.get(0).get(0);// sql
			Map<Integer, Object> paraMap = map.get(1);// 参数

			statement = connection.prepareStatement(sql);
			Object[] indexArray = paraMap.keySet().toArray();
			Arrays.sort(indexArray);
			for (Object objIndex : indexArray) {
				Integer index = (Integer) objIndex;
				Object object = paraMap.get(index);
				JdbcBeanUtil.setPreparedStatementVal(statement,index,object);
			}
			statement.execute();
			connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				log.error("", e1);
			}
			log.error("执行批量sql语句", e);
		} finally {
			try {
				connection.setAutoCommit(true);
			} catch (SQLException e) {
				log.error("", e);
			}
			JdbcConnection.close(null, statement, connection);
		}
		return executeCount;
	}
	/**
	 * 执行批量sql语句
	 * @param sql
	 * @return
	 */
	public static int[] executeBatch(String sql,Connection connection) {
		Statement statement = null;
		int[] executeCount = new int[0];
		try {
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			statement.addBatch(sql);
			executeCount = statement.executeBatch();
			connection.commit();
			connection.setAutoCommit(true);
		} catch (Exception e) {
			log.error("执行批量sql语句", e);
		} finally {
			JdbcConnection.close(null, statement, connection);
		}
		return executeCount;
	}
	/**
	 * 查询id list
	 */
	public static List<Long> getLongList(String sql,Connection connection) {
		List<Long> longList = new ArrayList<Long>();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {

			preparedStatement = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			preparedStatement.setFetchSize(Integer.MIN_VALUE); // 也可以修改jdbc url通过defaultFetchSize参数来设置，这样默认所以的返回结果都是通过流方式读取.
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				do {
					longList.add(resultSet.getLong(1));
				} while (resultSet.next());
			}
		} catch (Exception e) {
			log.error("查询异常", e);
		} finally {
			JdbcConnection.close(resultSet, preparedStatement, connection);
		}
		return longList;
	}
	
	/**
	 * 
	 * 按条件查找数据
	 * sql中以问好（?）存变量
	 * params中参数与sql中?一一对应
	 * @param clazz		查询结果类型（entity）
	 * @param sql		sql
	 * @param params	参数
	 * 
	 * @return
	 */
	public static <T> List<T> queryByConditon(Class<T> clazz, String sql,Connection connection){
		return queryByConditon(clazz, sql, null, null,connection);
	}

	
	/**
	 * 
	 * 按条件查找数据
	 * sql中以问好（?）存变量
	 * params中参数与sql中?一一对应
	 * @param clazz		查询结果类型（entity）
	 * @param sql		sql
	 * @param params	参数
	 * 
	 * @return
	 */
	public static <T> List<T> queryByConditon(Class<T> clazz,String sql,Integer fetchSize,Connection connection){
		return queryByConditon(clazz, sql, null, fetchSize,connection);
	}
	
	/**
	 * 
	 * 按条件查找数据
	 * sql中以问好（?）存变量
	 * params中参数与sql中?一一对应
	 * @param clazz		查询结果类型（entity）
	 * @param sql		sql
	 * @param params	参数
	 * @param fetchSize
	 * @param resultSetType
	 * @param resultSetConcurrency
	 * @param resultSetHoldability
	 * 
	 * @return
	 */
	public static <T> List<T> queryByConditon(Class<T> clazz, String sql, List<Object> params, Integer fetchSize,Connection connection) {
		List<T> resultList = new ArrayList<T>();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			if (preparedStatement == null) {
				log.error("获取preparedStatement失败！");
				return resultList;
			}
			if (fetchSize != null) {
				preparedStatement.setFetchSize(fetchSize);
			}else{
				preparedStatement.setFetchSize(Integer.MIN_VALUE);
			}
			if (CollectionUtils.isNotEmpty(params)) {
				for (int i = 0; i < params.size(); i++) {
					Object object = params.get(i);
					int parameterIndex = i+1;
					JdbcBeanUtil.setPreparedStatementVal(preparedStatement, parameterIndex, object);
				}
			}
			resultSet = preparedStatement.executeQuery();
			resultList = JdbcBeanUtil.getBeanFormResultSet(clazz, resultSet);
		} catch (Exception e) {
			log.error("通过反射查询数据封装到对象", e);
		} finally{
			JdbcConnection.close(resultSet, preparedStatement, connection);
		}
		return resultList;
	}
	
	/**
	 * 更新
	 * 
	 * @param entity
	 */
	public static void update(Object entity, Connection connection){
		Class<? extends Object> clazz = entity.getClass();
		//获取表名
		String tableName = JdbcBeanUtil.getTableName(clazz);
		if (StringUtils.isEmpty(tableName)) {
			String msg = "表名为空["+clazz+"]";
			log.debug(msg);
			return;
		}
		updateWithTableName(entity, tableName, connection);
	}
	
	/**
	 * 更新（表名为传入）
	 * 
	 * @param entity
	 */
	public static void updateWithTableName(Object entity,String tableName,Connection connection){
		if (StringUtils.isEmpty(tableName)) {
			log.error("表名为空["+tableName+"]");
			return;
		}
		Class<? extends Object> clazz = entity.getClass();
		Map<Method, EntityColumnBean> helpMap = JdbcBeanUtil.getMethodHelpMap(clazz);
		if (MapUtils.isEmpty(helpMap)) {
			log.debug("该对象中无对应数据库中子段["+clazz+"]");
			return;
		}
		Object id;
		try {
			Method getIdMethod = clazz.getMethod("getId");
			id = getIdMethod.invoke(entity);
			if (id == null) {
				log.debug("主键为空！");
				return;
			}
		} catch (NoSuchMethodException | SecurityException e) {
			log.error(e.toString(),e);
			return;
		} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
			log.error(e.toString(),e);
			return;
		}
		
		PreparedStatement statement = null;
		Set<Method> getMethodSet = helpMap.keySet();
		String strSQL = "UPDATE " + tableName + " SET #VALS #WHERE";
		StringBuffer values = new StringBuffer("");
		int i = 0;
		Map<Integer, Object> valMap = new HashMap<Integer, Object>();
		try {
			for (Method method : getMethodSet) {
				if (("getId").equals(method.getName())) {
					continue;
				}
				Object val = method.invoke(entity);
				if (val == null) {
					continue;
				}
				if (values.length() > 0 ) {
					values.append(" , ");
				}
				values.append(helpMap.get(method).getColumnNameDB() + " = ? ");
				valMap.put(++i, val);
			}
		} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
			log.error(e.toString());
		}
		strSQL = strSQL.replace("#WHERE", " WHERE id = ? ");
		valMap.put(++i, id);
		strSQL = strSQL.replace("#VALS", values);
		log.debug(" SQL: " + strSQL);
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(strSQL);
			Object[] indexArray = valMap.keySet().toArray();
			Arrays.sort(indexArray);
			for (Object objIndex : indexArray) {
				Integer index = (Integer) objIndex;
				Object val = valMap.get(index);
				JdbcBeanUtil.setPreparedStatementVal(statement,index,val);
			}
			statement.execute();
			connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				log.error("",e);
			}
			log.error("执行出错",e);
		} finally {
			try {
				connection.setAutoCommit(true);
			} catch (SQLException e) {
				log.error("",e);
			}
			JdbcConnection.close(null, statement, connection);
		}
	}
	
	/**
	 * 删除by 主键
	 * 
	 * @param id
	 * @param clazz
	 */
	public static void deleteById(Long id,Class<? extends Object> clazz,Connection connection){
		String tableName = JdbcBeanUtil.getTableName(clazz);
		if (StringUtils.isEmpty(tableName)) {
			log.debug("表名为空["+clazz+"]");
			return;
		}
		deleteByIdWithTableName(id,clazz,tableName,connection);
	}
	
	/**
	 * 删除by 主键（表名为参数）
	 * 
	 * @param id
	 * @param clazz
	 */
	public static void deleteByIdWithTableName(Long id,Class<? extends Object> clazz,String tableName,Connection connection){
		if (StringUtils.isEmpty(tableName)) {
			log.debug("表名为空["+clazz+"]");
			return;
		}
		String sql = "DELETE from " + tableName + " WHERE id = ? ";
		PreparedStatement preparedStatement = null;
		try {
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(sql);
			if (preparedStatement == null) {
				log.error("获取preparedStatement失败！");
				return;
			}
			preparedStatement.setLong(1, id);
			preparedStatement.execute();
			connection.commit();
		} catch (SQLException e){
			try {
				connection.rollback();
			} catch (SQLException e1) {
				log.error("",e1);
			}
			log.error("执行失败！",e);
		}finally {
			try {
				connection.setAutoCommit(true);
			} catch (SQLException e) {
				log.error("",e);
			}
			JdbcConnection.close(null, preparedStatement, connection);
		}
	}
	
	/**
	 * 批量按主键删除
	 * 
	 * @param ids
	 * @param clazz
	 */
	public static void deleteByIdList(List<Long> ids,Class<? extends Object> clazz){
		String tableName = JdbcBeanUtil.getTableName(clazz);
		deleteByIdListWhitTableName(ids,clazz,tableName);
	}
	
	/**
	 * 批量按主键删除(表名为入参)
	 * 
	 * @param ids
	 * @param clazz
	 * @param tableName
	 */
	public static void deleteByIdListWhitTableName(List<Long> ids,Class<? extends Object> clazz,String tableName){
		if (CollectionUtils.isEmpty(ids)) {
			return;
		}
		if (StringUtils.isEmpty(tableName)) {
			log.debug("表名为空["+clazz+"]");
			return;
		}
		String sql = "DELETE from " + tableName + " WHERE #CON";
		StringBuffer condition = new StringBuffer();
		Boolean flag = false;
		condition.append("id in(");
		for (int i = 0; i < ids.size(); i++) {
			if (flag) {
				condition.append(",");
			}
			condition.append("?");
			flag = true;
		}
		condition.append(")");
		sql = sql.replace("#CON", condition);
		
		Connection connection = JdbcConnection.getConnection();
		PreparedStatement preparedStatement = null;
		try {
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(sql);
			if (preparedStatement == null) {
				log.error("获取preparedStatement失败！");
				return;
			}
			for (int i = 0; i < ids.size(); i++) {
				preparedStatement.setLong(i+1, ids.get(i));
			}
			preparedStatement.execute();
			connection.commit();
		} catch (SQLException e){
			try {
				connection.rollback();
			} catch (SQLException e1) {
				log.error("", e1);
			}
			log.error("执行失败！",e);
		}finally {
			try {
				connection.setAutoCommit(true);
			} catch (SQLException e) {
				log.error("", e);
			}
			JdbcConnection.close(null, preparedStatement, connection);
		}
	}
	
	
	
	/**
	 * 保存
	 * 
	 * @param entity
	 */
	public static void save(Object entity,Connection connection){
		Class<? extends Object> clazz = entity.getClass();
		//获取表名
		String tableName = JdbcBeanUtil.getTableName(clazz);
		if (StringUtils.isEmpty(tableName)) {
			log.debug("表名为空["+clazz+"]");
			return;
		}
		Map<Method, EntityColumnBean> helpMap = JdbcBeanUtil.getMethodHelpMap(clazz);
		if (MapUtils.isEmpty(helpMap)) {
			log.debug("该对象中无对应数据库中子段["+clazz+"]");
			return;
		}
		try {
			Method getIdMethod = clazz.getMethod("getId");
			Object id = getIdMethod.invoke(entity);
			if (id == null) {
				log.debug("主键为空！");
				return;
			}
		} catch (NoSuchMethodException | SecurityException e) {
			log.error(e.toString(),e);
			return;
		} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
			log.error(e.toString(),e);
			return;
		}
		PreparedStatement statement = null;
		Set<Method> getMethodSet = helpMap.keySet();
		String strSQL = "INSERT INTO " + tableName + "(#COLS) VALUES (#VALS)";
		StringBuffer cols = new StringBuffer("");
		StringBuffer values = new StringBuffer("");
		int i = 0;
		Map<Integer, Object> valMap = new HashMap<Integer, Object>();
		try {
			for (Method method : getMethodSet) {
				Object val = method.invoke(entity);
				if (val == null) {
					continue;
				}
				if (values.length() > 0 ) {
					values.append(" , ");
					cols.append(" , ");
					
				}
				cols.append(helpMap.get(method).getColumnNameDB());
				values.append("?");
				valMap.put(++i, val);
			}
		} catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
			log.error(e.toString());
		}
		strSQL = strSQL.replace("#VALS", values).replace("#COLS", cols);	
		log.debug(" SQL: " + strSQL);
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(strSQL);
			Object[] indexArray = valMap.keySet().toArray();
			Arrays.sort(indexArray);
			for (Object objIndex : indexArray) {
				Integer index = (Integer) objIndex;
				Object val = valMap.get(index);
				JdbcBeanUtil.setPreparedStatementVal(statement,index,val);
			}
			statement.execute();
			connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				log.error("",e1);
			}
			log.error("执行出错",e);
		} finally {
			try {
				connection.setAutoCommit(true);
			} catch (SQLException e) {
				log.error("", e);
			}
			JdbcConnection.close(null, statement, connection);
		}
	}
	
}
